﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SQLite;
using System.Globalization;
using System.Linq;
using ResourceBlender.Core;

namespace ResourceBlender.DAL.Import
{
    public class RbtResourceImporter
    {
        /// <summary>
        /// Imports from an RBT translation database.
        /// </summary>
        /// <param name="log">The log handler.</param>
        /// <param name="fileName">The path of the file to import.</param>
        /// <param name="selectedLanguages">The codes of the languages to import.</param>
        public static void Import(StatusMessage.StatusChangedEventHandler log, string fileName,
                                  Dictionary<string, CultureInfo> selectedLanguages)
        {
            Database db = null;
            SQLiteConnection dbSqlite = null;
            int numUpdated = 0,
                numDeleted = 0;
            try
            {
                dbSqlite = new SQLiteConnection(Database.GetSQLiteConnectionString(fileName));
                dbSqlite.Open();
                using (SQLiteCommand cmdSqlite = dbSqlite.CreateCommand())
                {
                    db = new Database();
                    db.OpenConnection();
                    cmdSqlite.CommandText =
                        "SELECT resourceid, language, translation, translatorcomment FROM resources WHERE " +
                        db.GenerateInClause("language", selectedLanguages.Values.Select(info => info.Name).ToArray(),
                                            false);
                    using (SQLiteDataReader readerSqlite = cmdSqlite.ExecuteReader())
                    {
                        if (!readerSqlite.HasRows)
                        {
                            throw new SQLiteException(
                                "The file does not contain any translations into the target language");
                        }
                        using (DbTransaction trans = db.BeginTransaction())
                        {
                            using (Command cmdInsert = db.CreateCommand(trans)) // for inserting/updating translations
                            using (Command cmdDelete = db.CreateCommand(trans))
                            {
                                // for deleting empty translations
                                switch (db.InstanceType)
                                {
                                    case DatabaseType.MySQL:
                                        cmdInsert.CommandText =
                                            string.Format(
                                                "INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@rID, @lang, @trans, @comment) ON DUPLICATE KEY UPDATE translation = @trans, translatorcomment = @comment",
                                                db.TablePrefix);
                                        break;
                                    case DatabaseType.SQLServer:
                                        cmdInsert.CommandText =
                                            string.Format(
                                                "UPDATE {0}resources SET translation = @trans, translatorcomment = @comment WHERE resourceid = @rID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@rID, @lang, @trans, @comment)",
                                                db.TablePrefix);
                                        break;
                                    case DatabaseType.SQLite:
                                        cmdInsert.CommandText =
                                            string.Format(
                                                "INSERT OR REPLACE INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@rID, @lang, @trans, @comment)",
                                                db.TablePrefix);
                                        break;
                                }
                                cmdInsert.AddParameterWithValue("@rID", "");
                                cmdInsert.AddParameterWithValue("@lang", "");
                                cmdInsert.AddParameterWithValue("@trans", "");
                                cmdInsert.AddParameterWithValue("@comment", "");
                                cmdDelete.CommandText =
                                    string.Format(
                                        "DELETE FROM {0}resources WHERE resourceid = @rID AND language = @lang",
                                        db.TablePrefix);
                                cmdDelete.AddParameterWithValue("@rID", "");
                                cmdDelete.AddParameterWithValue("@lang", "");
                                while (readerSqlite.Read())
                                {
                                    try
                                    {
                                        string resourceId = readerSqlite["resourceid"].ToString(),
                                               language = readerSqlite["language"].ToString(),
                                               translation = readerSqlite["translation"].ToString(),
                                               translatorComment = readerSqlite["translatorcomment"].ToString();
                                        if (translation.Length == 0)
                                        {
                                            // delete the translation
                                            cmdDelete.Parameters["@rID"].Value = resourceId;
                                            cmdDelete.Parameters["@lang"].Value = language;
                                            int affected = cmdDelete.ExecuteNonQuery();
                                            if (affected > 0)
                                            {
                                                numDeleted += affected;
                                                log(
                                                    new StatusMessage(
                                                        string.Format("Deleted {0} translation for resource #{1}",
                                                                      selectedLanguages[language].EnglishName,
                                                                      resourceId),
                                                        StatusMessage.StatusMessageType.Success));
                                            }
                                        }
                                        else
                                        {
                                            cmdInsert.Parameters["@rID"].Value = resourceId;
                                            cmdInsert.Parameters["@lang"].Value = language;
                                            cmdInsert.Parameters["@trans"].Value = translation;
                                            if (translatorComment.Length == 0)
                                            {
                                                cmdInsert.Parameters["@comment"].Value = DBNull.Value;
                                            }
                                            else
                                            {
                                                cmdInsert.Parameters["@comment"].Value = translatorComment;
                                            }
                                            int affected = cmdInsert.ExecuteNonQuery();
                                            if (affected > 0)
                                            {
                                                numUpdated += affected;
                                                log(
                                                    new StatusMessage(
                                                        string.Format("Updated {0} translation for resource #{1}",
                                                                      selectedLanguages[language].EnglishName,
                                                                      resourceId),
                                                        StatusMessage.StatusMessageType.Success));
                                            }
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        log(
                                            new StatusMessage(
                                                string.Format("Error processing translation: {0}", ex.Message),
                                                StatusMessage.StatusMessageType.Error));
                                    }
                                }
                            }
                            trans.Commit();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
            }
            finally
            {
                if (db != null && db.Connected)
                {
                    try
                    {
                        db.CloseConnection();
                    }
                    catch
                    {
                    }
                }
                if (dbSqlite != null)
                {
                    try
                    {
                        dbSqlite.Close();
                    }
                    catch
                    {
                    }
                }
                log(
                    new StatusMessage(
                        string.Format("Updated {0} {1}, deleted {2} {3}", numUpdated,
                                      numUpdated == 1 ? "translation" : "translations", numDeleted,
                                      numDeleted == 1 ? "translation" : "translations"),
                        StatusMessage.StatusMessageType.Success));
            }
        }
    }
}